<?php
/**
 * classify品质报表
 */
namespace App\Logic\Quality;

use App\Models\Basics\SysICountry;
use App\Models\Product\ProductAmazonSkuCCS;
use App\Models\Quality\QDetail;
use App\Models\Quality\QReport;
use App\Models\Quality\QReportClassify;
use Illuminate\Support\Facades\DB;
class ReportC
{
    public static function init($date)
    {
        try{
            $time = strtotime($date);
            $_start = date('Y-m-01',$time);
            $_end = date('Y-m-t',$time);
            $area = [];

            //Amazon销售
            ProductAmazonSkuCCS::query()
                ->orderBy('id')
                ->chunk(300,function ($_res) use (&$area,$date,$_start,$_end){
                    DB::beginTransaction();
                    foreach($_res as $_re){
                        $where = [];
                        $where['report_date'] = $date;
                        $where['country_id'] = $_re->country_id;
                        $where['classify_id'] = $_re->classify_id;
                        $temp = [];
                        if(!array_key_exists($_re->country_id,$area)){
                            $area[$_re->country_id] = SysICountry::getAreaIdByCountryId($_re->country_id);
                        }
                        $temp['area_id'] = $area[$_re->country_id];
                        $num = self::_num($date,$_re->country_id,$_re->classify_id);
                        $temp['sales_num'] = $num->sales_num ?? 0; //当月销售总量
                        $temp['return_num'] = $num->return_num ?? 0; //当月退货总量
                        $temp['saleable_num'] = $num->saleable_num ?? 0; //可二次销售总量
                        $temp['size'] = self::_problem($_start,$_end,$_re->country_id,$_re->classify_id,1);
                        $temp['produce'] = self::_problem($_start,$_end,$_re->country_id,$_re->classify_id,2);
                        $temp['design'] = self::_problem($_start,$_end,$_re->country_id,$_re->classify_id,3);
                        $temp['subject'] = self::_problem($_start,$_end,$_re->country_id,$_re->classify_id,4);
                        QReportClassify::query()->updateOrCreate($where,$temp);
                    }
                    DB::commit();
            });
        }catch (\Exception $e){
            DB::rollBack();
            report($e);
        }
    }

    /**
     * sku品类数量 （sales_num:当月销售总量，return_num：当月退货总量，saleable_num：可二次销售总量）
     * @param $date
     * @param $countryId
     * @param $classifyId
     * @return \Illuminate\Database\Eloquent\Builder|\Illuminate\Database\Eloquent\Model|object|null
     */
    private static function _num($date,$countryId,$classifyId){
        return QReport::query()
            ->where('report_date',$date)
            ->where('country_id',$countryId)
            ->whereHas('sku', function ($query) use($classifyId){
                $query->where('classify_id', $classifyId);
            })
            ->select(DB::raw("sum(sales_num) as sales_num,sum(return_num) as return_num,sum(saleable_num) as saleable_num"))
            ->first();
    }

    /**
     * （标签）问题数量
     * @param $start
     * @param $end
     * @param $countryId
     * @param $classifyId
     * @param $type
     * @return array
     */
    private static function _problem($start,$end,$countryId,$classifyId,$type){
        return QDetail::query()
            ->where('report_date','>=',$start)
            ->where('report_date','<=',$end)
            ->where('country_id',$countryId)
            ->where('classify_id',$classifyId)
            ->where('type',$type)
            ->groupBy('tag')
            ->select('tag',DB::raw('sum(num) as num'))
            ->pluck('num','tag')
            ->toArray();
    }
}
